 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sprUpdate_NameRelationData')
	BEGIN
		DROP  Procedure  sprUpdate_NameRelationData
	END

GO

CREATE Procedure sprUpdate_NameRelationData
	@nameGuid uniqueidentifier,
	@user nvarchar(50)
AS

	--update parent and preferred fks
	declare @parentFk uniqueidentifier
	--try system PN rec
	select top 1 @parentfk = p.PNNameFk
	from vwProviderName pn
	inner join tblProviderName ppn on ppn.PNPk = pn.PNPk
	inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
	inner join vwProviderConceptRelationship pcr on pcr.PCRConcept1Id = pc.PCConceptId and PCR.ProviderPk = pc.ProviderPk
	inner join vwProviderConcept pc2 on pc2.PCConceptId = pcr.PCRConcept2Id and pc2.ProviderPk = pcr.ProviderPk
	inner join vwProviderName p on p.PNNameId = pc2.PCName1Id and p.ProviderPk = pc.ProviderPk
	where ppn.PNNameFk = @nameGuid and p.ProviderIsEditor = 1 and pcr.PCRRelationshipFk = 6 --parent
	
	if (@parentFk is null)
	begin
		--try parentage PN rec
		select top 1 @parentfk = p.PNNameFk
		from vwProviderName pn
		inner join tblProviderName ppn on ppn.PNPk = pn.PNPk
		inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
		inner join vwProviderConceptRelationship pcr on pcr.PCRConcept1Id = pc.PCConceptId and PCR.ProviderPk = pc.ProviderPk
		inner join vwProviderConcept pc2 on pc2.PCConceptId = pcr.PCRConcept2Id and pc2.ProviderPk = pcr.ProviderPk
		inner join vwProviderName p on p.PNNameId = pc2.PCName1Id and p.ProviderPk = pc.ProviderPk
		inner join tblProvider pr on pr.ProviderPk = p.ProviderPk
		where ppn.PNNameFk = @nameGuid and pr.ProviderUseForParentage = 1 and pcr.PCRRelationshipFk = 6 --parent	
	end
	
	if (@parentFk is null)
	begin
		--use majority
		select top 1 @parentfk = v.val
		from tblName n
		inner join (select p.PNNameFk as val, count(*) as c
			from vwProviderName pn
			inner join tblProviderName ppn on ppn.PNPk = pn.PNPk
			inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
			inner join vwProviderConceptRelationship pcr on pcr.PCRConcept1Id = pc.PCConceptId and PCR.ProviderPk = pc.ProviderPk
			inner join vwProviderConcept pc2 on pc2.PCConceptId = pcr.PCRConcept2Id and pc2.ProviderPk = pcr.ProviderPk
			inner join vwProviderName p on p.PNNameId = pc2.PCName1Id and p.ProviderPk = pc.ProviderPk
			where ppn.PNNameFk = @nameGuid and pcr.PCRRelationshipFk = 6 --parent rel
			group by p.pnnamefk
			) v on v.val = n.NameGuid
		order by c desc	
	end

	--only update if not null, othewise it is probably currently pointing to the 'unknown' node
	if (@parentFk is not null)
	begin
		update n
		set n.NameParentFk = @parentFk, n.NameParent = pn.NameFull
		from tblName n
		inner join tblName pn on pn.NameGuid = @parentFk
		where n.NameGuid = @nameGuid				
	end


	--preferred	
	declare @prefFk uniqueidentifier, @hasSys bit
	set @hasSys = 0
	--try system PN rec
	select top 1 @prefFk = p.PNNameFk, @hasSys = 1
	from vwProviderName pn
	inner join tblProviderName ppn on ppn.PNPk = pn.PNPk
	inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
	inner join vwProviderConceptRelationship pcr on pcr.PCRConcept1Id = pc.PCConceptId and PCR.ProviderPk = pc.ProviderPk
	inner join vwProviderConcept pc2 on pc2.PCConceptId = pcr.PCRConcept2Id and pc2.ProviderPk = pcr.ProviderPk
	inner join vwProviderName p on p.PNNameId = pc2.PCName1Id and p.ProviderPk = pc.ProviderPk
	where ppn.PNNameFk = @nameGuid and pc.ProviderIsEditor = 1 and pcr.PCRRelationshipFk = 15 --preferred
		
	if (@prefFk is null and @hasSys = 0)
	begin
		--use majority
		select top 1 @prefFk = v.val
		from tblName n
		inner join (select p.PNNameFk as val, count(*) as c
			from vwProviderName pn
			inner join tblProviderName ppn on ppn.PNPk = pn.PNPk
			inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
			inner join vwProviderConceptRelationship pcr on pcr.PCRConcept1Id = pc.PCConceptId and PCR.ProviderPk = pc.ProviderPk
			inner join vwProviderConcept pc2 on pc2.PCConceptId = pcr.PCRConcept2Id and pc2.ProviderPk = pcr.ProviderPk
			inner join vwProviderName p on p.PNNameId = pc2.PCName1Id and p.ProviderPk = pc.ProviderPk
			where ppn.PNNameFk = @nameGuid and pcr.PCRRelationshipFk = 15 
			group by p.pnnamefk
			) v on v.val = n.NameGuid
		order by c desc
	end

	update n
	set n.NamePreferredFk = @prefFk, n.NamePreferred = pn.NameFull
	from tblName n
	left join tblName pn on pn.NameGuid = @prefFk
	where n.NameGuid = @nameGuid		
	
	
GO
	
GRANT EXEC ON sprUpdate_NameRelationData TO PUBLIC

GO